package data;

import java.util.LinkedList;
import java.util.List;

import models.Attendant;
import models.Student;

import org.sql2o.Query;
import org.sql2o.Sql2o;
import org.sql2o.StatementRunnable;
import org.sql2o.data.Row;
import org.sql2o.data.Table;


public class AttendantDAO implements IAttendantDAO {
	private static Sql2o database;
	static {
		database = new Connection().open();
	}
	
	public List<Integer> getStudentIds(int courseId){
		final String sql = "select id from Person where id in (select studentId from StudentInCourse where courseId = :courseId)";
		Table table = database.createQuery(sql).addParameter("courseId", courseId).executeAndFetchTable();
		List<Integer> ids = new LinkedList<>();
		for(Row r : table.rows()){
			ids.add(r.getInteger("id"));
		}
		
		return ids;
	}
	
	public void updateAttendants(final List<Attendant> attendants){
	    final String sql = "UPDATE Attendant set status = :status WHERE  id = :id";
	    
	    database.runInTransaction(new StatementRunnable() {
			@Override
			public void run(org.sql2o.Connection connection, Object argument)
					throws Throwable {	
				
				
	            Query query = connection.createQuery(sql);
	            if(attendants != null){
	            	for(Attendant a : attendants){
	            		query.addParameter("status", a.getStatus()).addParameter("id", a.getId()).addToBatch();
	            	}
	            }
	            query.executeBatch();		
			}
	    });
	}
	
	public void generateAttendants(final int courseId, final int sessionId, final List<Integer> studentIds){
	    final String sql = "INSERT INTO Attendant(studentId, sessionId, courseId, status) VALUES (:studentId, :sessionId, :courseId, 0)";
	    
	    database.runInTransaction(new StatementRunnable() {
			@Override
			public void run(org.sql2o.Connection connection, Object argument)
					throws Throwable {	
				
				Query updateSessionQuery = connection.createQuery("Update TeachingSession set isHavingRecord = 1 where id = :id");
				
				updateSessionQuery.addParameter("id", sessionId).executeUpdate();
				
	            Query query = connection.createQuery(sql);
	            if(studentIds != null){
	            	for(Integer id : studentIds){
	            		query.addParameter("studentId", id).addParameter("sessionId", sessionId).addParameter("courseId", courseId).addToBatch();
	            	}
	            }
	            query.executeBatch();		
			}
	    });
	}
	
	public List<Attendant> listTutorAttendants(int sessionId, int tutorId) {
		final String sql = "select a.id, a.status, s.id as 'studentId', s.firstName, s.lastName from Attendant a join Person s on a.studentId = s.id where sessionId = :sessionId and studentId in (select studentId from StudentOfTutor where tutorId = :tutorId)";
		Table table = database.createQuery(sql)
				.addParameter("sessionId", sessionId)
				.addParameter("tutorId", tutorId)
				.executeAndFetchTable();
		List<Attendant> attendants = new LinkedList<>();
		for(Row r : table.rows()){
			Attendant a = new Attendant();
			a.setId(r.getInteger("id"));
			a.setStatus(r.getInteger("status"));
			Student student = new Student();
			student.setId(r.getInteger("studentId"));
			student.setFirstName(r.getString("firstName"));
			student.setLastName(r.getString("lastName"));
			a.setStudent(student);
			attendants.add(a);
		}
		return attendants;
	}
	
	public List<Attendant> listStudentAttendants(int sessionId, int studentId){
		final String sql = "select a.id, a.status, s.id as 'studentId', s.firstName, s.lastName from Attendant a join Person s on a.studentId = s.id where sessionId = :sessionId and studentId = :studentId";
		Table table = database.createQuery(sql)
				.addParameter("sessionId", sessionId)
				.addParameter("studentId", studentId)
				.executeAndFetchTable();
		List<Attendant> attendants = new LinkedList<>();
		for(Row r : table.rows()){
			Attendant a = new Attendant();
			a.setId(r.getInteger("id"));
			a.setStatus(r.getInteger("status"));
			Student student = new Student();
			student.setId(r.getInteger("studentId"));
			student.setFirstName(r.getString("firstName"));
			student.setLastName(r.getString("lastName"));
			a.setStudent(student);
			attendants.add(a);
		}
		return attendants;
	}
	
	public List<Attendant> list(int sessionId) {
		final String sql = "select a.id, a.status, s.id as 'studentId', s.firstName, s.lastName from Attendant a join Person s on a.studentId = s.id where sessionId = :sessionId";
		Table table = database.createQuery(sql)
				.addParameter("sessionId", sessionId)
				.executeAndFetchTable();
		List<Attendant> attendants = new LinkedList<>();
		for(Row r : table.rows()){
			Attendant a = new Attendant();
			a.setId(r.getInteger("id"));
			a.setStatus(r.getInteger("status"));
			Student student = new Student();
			student.setId(r.getInteger("studentId"));
			student.setFirstName(r.getString("firstName"));
			student.setLastName(r.getString("lastName"));
			a.setStudent(student);
			attendants.add(a);
		}
		return attendants;
	}
	
	public void delete(int id) {
		String sql = "delete from Attendant where id = :id";
		database.createQuery(sql)
				.addParameter("id", id).executeUpdate();
		
	}
	
	public Object insert(int studentId, int sessionId, int courseId, int status){
		String sql = "insert into Attendant(studentId, sessionId, courseId, status) values(:studentId, :sessionId, :courseId, :status)";
		Object result = database.createQuery(sql)
			.addParameter(":studentId", studentId)
			.addParameter(":sessionId", sessionId)
			.addParameter(":courseId", courseId)
			.addParameter(":status", status)
			.executeUpdate().getKey();
		return result;
	}
	
}
